package com.hcrazy.manchester.app.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.hcrazy.manchester.app.pojo.CouponAddress;
import com.hcrazy.manchester.app.pojo.CouponSeller;
import com.hcrazy.manchester.app.pojo.PageModel;

public class CouponSellerDAO extends BaseDAO<CouponSeller>{

	public int addCouponSeller(CouponSeller seller) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into him_coupon_seller(");
		sb.append("name,description_mc,thumbnail,link,createDate,userId,updateDate,sortId");
		sb.append(") values (");
		sb.append(":name,:description_mc,:thumbnail,:link,:createDate,:userId,:updateDate,:sortId");
		sb.append(")");
		
		update(sb.toString(), seller);
		
		return getIncreamentId();
	}
	
	public void updateCouponSeller(CouponSeller seller) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_coupon_seller set ");
		sb.append(" name=:name,description_mc=:description_mc,thumbnail=:thumbnail,link=:link,");
		sb.append(" createDate=:createDate,userId=:userId,updateDate=:updateDate,sortId=:sortId");
		sb.append(" where sellerId = :sellerId");
		
		update(sb.toString(), seller);
	}
	
	public List<CouponSeller> listAllInfos() throws Exception{
		String sql = "select * from him_coupon_seller";
		return getList(sql, CouponSeller.class);
	}
	
	public List<CouponSeller> getAllInfosBySortId(int sortId) throws Exception{
		String sql = "select a.*,b.menuName sortName from him_coupon_seller a,him_book_menu b where a.sortId = b.menuId and a.sortId = ?";
		return getList(sql, CouponSeller.class,sortId);
	}
	
	public PageModel getPagedInfos(PageModel model,String search,int sortId) throws Exception{
		StringBuilder querySQL = new StringBuilder("select * from (select a.*,b.menuName sortName from him_coupon_seller a,him_book_menu b where a.sortId = b.menuId) as c");
		StringBuilder countSQL = new StringBuilder("select count(*) from (select a.*,b.menuName sortName from him_coupon_seller a,him_book_menu b where a.sortId = b.menuId) as c ");
		StringBuilder whereSQL = new StringBuilder(" where (name like ?  or link like ?) ");
		
		String orderBy = " order by updateDate desc";
		
		List<Object> paramList = new ArrayList<Object>();
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");
		if(sortId > 0){
			whereSQL.append(" and sortId = ?");
			paramList.add(sortId);
		}
		
		whereSQL.append(orderBy);
		
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, CouponSeller.class);
	}
	
	public CouponSeller getById(int sellerId) throws Exception{
		String sql = "select * from him_coupon_seller where sellerId = ?";
		return this.getJavaBean(sql, CouponSeller.class, sellerId);
	}
	
	public void delCouponSeller(int sellerId) throws Exception{
		String sql = "delete from him_coupon_seller where sellerId = ?";
		this.commonUpdate(sql, sellerId);
	}
	
	public void addAddresses(List<CouponAddress> addresses,int sellerId) throws Exception{
		if(null != addresses && !addresses.isEmpty()){
			StringBuilder sb = new StringBuilder();
			sb.append("insert into him_coupon_address(");
			sb.append("address,tel,postCode,sellerId");
			sb.append(") values (");
			sb.append("?,?,?,?");
			sb.append(")");
			List<Object[]> batchArgs = new ArrayList<Object[]>();
			for(CouponAddress address:addresses){
				batchArgs.add(new Object[]{address.getAddress(),address.getTel(),address.getPostCode(),sellerId});
			}
			//批量添加
			getJdbcTemplate().batchUpdate(sb.toString(), batchArgs);
		}
	}
	
	public List<CouponAddress> getAddressesBySellerId(int sellerId) throws Exception{
		String sql = "select * from him_coupon_address where sellerId = ?";
		
		return getJdbcTemplate().query(sql, new Object[]{sellerId}, new RowMapper<CouponAddress>(){
			@Override
			public CouponAddress mapRow(ResultSet rs, int arg1)
					throws SQLException {
				CouponAddress address = new CouponAddress();
				address.setAddress(rs.getString("address"));
				address.setAddressId(rs.getInt("addressId"));
				address.setPostCode(rs.getString("postCode"));
				address.setSellerId(rs.getInt("sellerId"));
				address.setTel(rs.getString("tel"));
				return address;
			}
			
		});
	}
	
	public void deleteAddreses(int sellerId) throws Exception{
		String sql = "delete from him_coupon_address where sellerId = ?";
		commonUpdate(sql, sellerId);
	}
	
}
